const db = require('../config/db');

class ClassModel {
  static async getClassesByTeacherId(teacherId) {
    const sql = `
      SELECT 
        c.id,
        c.name,
        c.status,
        c.semester AS semester,
        co.course_code AS courseCode,
        co.course_name AS courseName,
        (SELECT COUNT(*) FROM class_students cs WHERE cs.class_id = c.id) AS studentCount,
        (SELECT COUNT(*) FROM experiment_classes ec WHERE ec.class_id = c.id) AS experimentCount
      FROM classes c
      JOIN courses co ON c.course_id = co.id
      WHERE c.teacher_id = ?
      ORDER BY c.semester DESC
    `;

    try {
      const [rows] = await db.query(sql, [teacherId]);
      return rows;
    } catch (error) {
      console.error('数据库查询错误:', error);
      throw new Error('获取班级数据失败');
    }
  }

  static async getClassByNameAndTeacher(name, teacherId) {
    const sql = `
      SELECT 
        id,
        name
      FROM classes
      WHERE name = ? AND teacher_id = ?
    `;
    try {
      const [rows] = await db.query(sql, [name, teacherId]);
      return rows[0];
    } catch (error) {
      console.error('数据库查询错误:', error);
      throw new Error('获取班级数据失败');
    }
  }

  // 创建班级
  static async createClass(classData) {
    const {
      id,
      name,
      semester,
      teacherId,
      courseId,
      status
    } = classData;

    try {
      const [result] = await db.query(
        `INSERT INTO classes 
         (id, name, semester, teacher_id, course_id, status) 
         VALUES (?, ?, ?, ?, ?, ?)`,
        [id, name, semester, teacherId, courseId, status]
      );
      return result;
    } catch (error) {
      console.error('创建班级失败:', error);
      throw new Error('创建班级失败');
    }
  }

  // 添加学生到班级
  static async addStudentsToClass(classId, studentIds) {
    if (!studentIds.length) return;

    const values = studentIds.map(studentId => [classId, studentId]);

    await db.query(
      `INSERT INTO class_students (class_id, student_id) 
       VALUES ?`,
      [values]
    );
  }

  // 删除班级
  static async deleteClass(classId) {
    const connection = await db.getConnection();
    try {
      await connection.beginTransaction();
      await connection.query(
        `DELETE FROM class_students WHERE class_id = ?`,
        [classId]
      );
      const [result] = await connection.query(
        `DELETE FROM classes WHERE id = ?`,
        [classId]
      );
      await connection.commit();
      return result.affectedRows > 0;
    } catch (error) {
      await connection.rollback();
      throw new Error('删除班级失败');
    } finally {
      connection.release();
    }
  }

  static async getExpsForClass(classId) {
    const [experiments] = await db.query(
      `SELECT e.id, e.title, ec.deadline
       FROM experiment_classes ec
       JOIN experiments e ON ec.experiment_id = e.id
       WHERE ec.class_id = ?`,
      [classId]
    );
    console.log("classModel: experiments", experiments);
    return experiments;
  }

  static async getStudentsForClass(classId) {
    const [students] = await db.query(`
        SELECT s.id, s.s_no, s.s_name, s.email, s.class
        FROM students s
        JOIN class_students cs ON s.id = cs.student_id
        WHERE cs.class_id = ?
    `, [classId]);

    console.log("classModel: students", students);
    return students;
  }

  // 获取实验分配记录
  static async getExperimentAssignment(classId, experimentId) {
    try {
      const [rows] = await db.query(
        `SELECT * FROM experiment_classes 
             WHERE class_id = ? AND experiment_id = ?`,
        [classId, experimentId]
      );
      return rows[0] || null;
    } catch (error) {
      console.error('查询实验分配失败:', error);
      throw new Error('查询实验分配失败');
    }
  }

  // 分配实验到班级
  static async assignExperiment(classId, experimentId, deadline) {
    try {
      await db.query(
        `INSERT INTO experiment_classes 
             (class_id, experiment_id, deadline) 
             VALUES (?, ?, ?)`,
        [classId, experimentId, deadline]
      );
    } catch (error) {
      console.error('分配实验失败:', error);
      throw new Error('分配实验失败');
    }
  }

  // 更新截止日期
  static async updateDeadline(classId, experimentId, deadline) {
    try {
      await db.query(
        `UPDATE experiment_classes 
             SET deadline = ? 
             WHERE class_id = ? AND experiment_id = ?`,
        [deadline, classId, experimentId]
      );
    } catch (error) {
      console.error('更新截止日期失败:', error);
      throw new Error('更新截止日期失败');
    }
  }

  // 移除实验分配
  static async removeExperiment(classId, experimentId) {
    try {
      await db.query(
        `DELETE FROM experiment_classes 
             WHERE class_id = ? AND experiment_id = ?`,
        [classId, experimentId]
      );
    } catch (error) {
      console.error('移除实验失败:', error);
      throw new Error('移除实验失败');
    }
  }

  // 移除班级学生
  static async removeStudentFromClass(classId, studentId) {
    try {
      await db.query(
        `DELETE FROM class_students 
       WHERE class_id = ? AND student_id = ?`,
        [classId, studentId]
      );
    } catch (error) {
      console.error('从班级移除学生失败:', error);
      throw new Error('从班级移除学生失败');
    }
  }
}

module.exports = ClassModel;